Release 10.1A: OpenEdge Data Management:
DataServer for ORACLE

Query tuning

In addition to the standard approach of using selection criteria to refine access to data, you can further influence how the DataServer executes a query through the Progress QUERY-TUNING phrase. How you structure a query determines how efficiently you access a database. Efficient use of the ORACLE RDBMS enhances the performance of DataServer applications. You can include the QUERY-TUNING phrase for the following Progress 4GL statements:

FOR EACH

Syntax

FOR EACH table QUERY-TUNING ( query-tuning-option query-tuning-option ... )

OPEN QUERY

Syntax

OPEN QUERY query QUERY-TUNING ( query-tuning-option query-tuning-option ... )

DO PRESELECT

Syntax

DO PRESELECT table QUERY-TUNING ( query-tuning-option query-tuning-option ... )

REPEAT PRESELECT

Syntax

REPEAT PRESELECT table QUERY-TUNING ( query-tuning-option query-tuning-option ... )

Place the QUERY-TUNING phrase after the last record phrase. For example, place it near the end of the statement where you also place block modifier phrases such as BREAK, ON ERROR, and TRANSACTION. Separate multiple query-tuning options by a single space. The QUERY-TUNING options have equivalent startup parameters. You cannot use the startup parameters to override the QUERY-TUNING settings.

Table 4–1 describes the query-tuning options.

Table 4–1: Query-tuning options

Option	Description
ARRAY-MESSAGE NO-ARRAY-MESSAGE	Specifies whether the DataServer sends multiple result rows in a single logical network message, thereby reducing network traffic. Default: ARRAY-MESSAGE, if the query uses a lookahead cursor.
BIND-WHERE NO-BIND-WHERE	Specifies whether the DataServer uses ORACLE bind variables for values in WHERE clauses. Using bind variables typically improves performance, but ORACLE provides unexpected results for some operations, such as a MATCHES on an indexed field and a trailing wild card or comparisons of CHAR fields that use ORACLE’s blank-padding rules. Specify NO-BIND-WHERE to use literals. Using NO-BIND-WHERE in queries that do comparisons (MATCHES, BEGINS on an indexed field) can improve performance. Default: BIND-WHERE.
CACHE-SIZE integer BYTE CACHE-SIZE integer ROW	Specifies the size of the cache for information (in bytes or records) used by lookahead or standard cursors. If you have two Progress 4GL statements that cause the DataServer to generate identical SQL code except that the second statement specifies a smaller cache size, the DataServer reuses the larger cache from the first statement if the cursor is still available. Reusing cache and cursors improves performance. Byte maximum: 65535 bytes. Byte minimum: Specify the number of bytes contained in a single record. For joins, specify the number of bytes contained in two joined records. By default, the DataServer sizes the cache to accommodate one record or, for a join, two joined records. For example, if a join returns a 500-byte record, you need a cache of at least 1000 bytes. Default: 1024 bytes with standard cursors; 8192 with lookahead cursors. Row maximum: the number of records that can fit in 65535 bytes. See the "Caching records" section for more information. Row minimum: 1 for a single table; 2 for a join.
DEBUG EXTENDED DEBUG SQL NO-DEBUG	Specifies whether the DataServer should print debugging information that it generates for the query to the dataserv.lg file. Specify DEBUG SQL to print the SQL that the DataServer executes against the ORACLE DBMS. Specify DEBUG EXTENDED to print additional information, such as cursor statistics. There are additional options for collecting advanced statistics with DEBUG. See the "Analyzing performance" section for more information. Default: NO-DEBUG.
HINT string1 string2 string3	Specifies the ORACLE hint syntax that the DataServer passes directly to the ORACLE DBMS as part of the query. This allows you to control which hints are passed as opposed to the index hints that the DataServer passes when appropriate. When you have to specify an index name in the hint syntax, use the name defined in the ORACLE database. Because the DataServer generates aliases for ORACLE tables using names from T0 through T9, use these aliases to refer to tables in the hint syntax. The DataServer passes the opening symbols (/*+) and closing symbols (*/). For example, to pass the /*+ORDERED*/ hint syntax, you specify only HINT "ORDERED". Passing incorrect hint syntax, inappropriate hints, or conflicting hints will not return an error but might give you unpredictable results. See your ORACLE documentation for information on hint syntax.
INDEX-HINT NO-INDEX-HINT	Specifies whether the DataServer should provide index hints to the ORACLE DBMS. Generally, index hints improve performance, but ORACLE’s responses to hints vary between releases. Specify NO-INDEX-HINT to test whether performance for a query improves when the DataServer executes it without hints. See the "Indexes" section for more information on index hints. By default, the DataServer passes index hints. You can turn off the default globally at compile time or run time by specifying the -noindexhint startup parameter when you start an OpenEdge session. Use INDEX-HINT to retain the behavior for individual queries. Default: INDEX-HINT.
JOIN-BY-SQLDB NO-JOIN-BY-SQLDB	Specifies whether the DataServer allows the ORACLE DBMS to perform a join, which usually improves performance. Default: JOIN-BY-SQLDB. You can turn off the JOIN-BY-SQLDB default globally at compile time by specifying the -nojoinbysqldb startup parameter when you start an OpenEdge session. The -nojoinbysqldb parameter does not override the explicit use of JOIN-BY-SQLDB in the QUERY-TUNING phrase.
LOOKAHEAD NO-LOOKAHEAD	Specifies whether the DataServer uses lookahead or standard cursors. Lookahead cursors fetch as many records as fit in the allocated cache (CACHE-SIZE), which limits the number of database accesses, thereby improving performance. Using lookahead cursors results in behavior that is different from the Progress 4GL because the client does not see any changes made to the records in the cache. Specify NO-LOOKAHEAD for behavior that is consistent with the Progress 4GL. Default: LOOKAHEAD, except with FIND statements and statements that use an EXCLUSIVE lock.
ORDERED-JOIN	Specifies that the DataServer embed the ORDERED hint syntax in the SQL it generates.
REVERSE-FROM	Specifies that ORACLE join tables in the reverse order in which they appear in the FROM clause. The DataServer generates a new SQL FROM clause with the tables in reverse order. REVERSE-FROM is independent of the ORDERED-JOIN option.

Note: All of the query-tuning options take effect at both compile and run time except for the INDEX-HINT, JOIN-BY-SQLDB, and NO-JOIN-BY-SQLDB options, which apply only at compile time.

The following example shows how to use the QUERY-TUNING phrase to enhance performance. It includes a join which the DataServer instructs ORACLE to perform by default. The QUERY-TUNING options specify that no lookahead cursors will be used. In addition, the DataServer will write an extended report on the SQL statements it executes, as shown:

FOR EACH customer, EACH order OF customer WHERE order-num > 20 BY cust-num QUERY-TUNING(NO-LOOKAHEAD DEBUG EXTENDED) TRANSACTION:

This example shows how to use the QUERY-TUNING phrase to manage cache size so that the DataServer can reuse cursors and cache, thereby improving performance. The phrase also passes a hint to the ORACLE optimizer to choose the cost-based approach to optimize the statement for best response time. Finally, the DEBUG EXTENDED option causes the DataServer to report on the SQL statements it executes, as shown:

FOR EACH customer, EACH order OF customer WHERE order-num > 20 QUERY-TUNING(CACHE-SIZE 20 ROW HINT "FIRST_ROWS" DEBUG EXTENDED) TRANSACTION: